package conn;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import java.util.List;

import com.google.gson.Gson;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import com.mysql.jdbc.CallableStatement;
import com.mysql.jdbc.Connection;

import conn.Connection1;

/*
 @return Connection 对象
 * */
public class Connection1{
	public static  Connection getConnection(){
		Connection conn = null ;
		try {
			// 加载数据库驱动，注册到驱动管理器 ,本地数据
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3316/test";
			String username = "root";
			String password = "123";
			// 加载数据库驱动，注册到驱动管理器 ，服务器数据库
			/*Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://175.155.13.23:3366/test?useUnicode=true&characterEncoding=UTF-8";
			String username = "root";
			String password = "wolfword";*/
			conn =  (Connection) DriverManager.getConnection(url,username,password);
		}catch (SQLException e) {
			e.printStackTrace();
		}
		catch (ClassNotFoundException e){
			e.printStackTrace();
		}
		return conn;
	}
	
	
	//实例化img,调用存储过程
	public List<img> findImg(){
		List<img> list = new ArrayList<img>();
		Connection conn = getConnection();
		try {
			CallableStatement cs = (CallableStatement) conn.prepareCall("{call FindImg()}");		
			ResultSet rs = cs.executeQuery();
			while(rs.next()){
				// 实例化img对象
				img img = new img();
				img.setid(rs.getInt("id"));
				img.setimgName(rs.getString("imgName"));
				img.setimgSrc(rs.getString("imgSrc"));
				img.setimgOtherName(rs.getString("imgOtherName"));
				list.add(img);
			}
			rs.close();		// 关闭ResultSet
			conn.close();   //关闭连接
		} catch (Exception e) {
			e.printStackTrace();
		}
		/*Gson gson= new Gson();
		String json = gson.toJson(list);
		System.out.println(json);*/
		// 返回list
		return list ;
	}
	
	/**
	 * 分页查询所有商品信息
	 * @param page 页数
	 * @return List<Product>
	 */
	public List<img> find(int page){
		// 创建List
		List<img> list = new ArrayList<img>();
		// 获取数据库连接
		Connection conn = getConnection();
		// 分页查询的SQL语句
		String sql = "select * from img order by id desc limit ?,?";
		try {
			// 获取PreparedStatement
			PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
			// 对SQL语句中的第1个参数赋值
			ps.setInt(1, (page - 1) * img.PAGE_SIZE);
			// 对SQL语句中的第2个参数赋值
			ps.setInt(2, img.PAGE_SIZE);
			// 执行查询操作
			ResultSet rs = ps.executeQuery();
			// 光标向后移动，并判断是否有效
			while(rs.next()){
				img img = new img();
				img.setid(rs.getInt("id"));
				img.setimgName(rs.getString("imgName"));
				img.setimgSrc(rs.getString("imgSrc"));
				img.setimgOtherName(rs.getString("imgOtherName"));
				list.add(img);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 查询总记录数
	 * @return 总记录数
	 */
	public int findCount(){
		// 总记录数
		int count = 0;
		// 获取数据库连接
		Connection conn = getConnection();
		// 查询总记录数SQL语句
		String sql = "select count(*) from img";
		try {
			// 创建Statement
			Statement stmt = (Statement) conn.createStatement();
			// 查询并获取ResultSet
			ResultSet rs = stmt.executeQuery(sql);
			// 光标向后移动，并判断是否有效
			if(rs.next()){
				// 对总记录数赋值
				count = rs.getInt(1);
			}
			// 关闭ResultSet
			rs.close();
			// 关闭Connection
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 返回总记录数
		return count;
	}
	
	/**
	 * 展示图片详情
	 * @param id 图片id
	 * @return List<Product>
	 */
	public List<img> findCurrImg(int id){
		// 创建List
		List<img> list = new ArrayList<img>();
		// 获取数据库连接
		Connection conn = getConnection();
		// 分页查询的SQL语句
		String sql = "select * from img where id = ?";
		try {
			PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
			ps.setInt(1,id);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				img img = new img();
				img.setid(rs.getInt("id"));
				img.setimgName(rs.getString("imgName"));
				img.setimgSrc(rs.getString("imgSrc"));
				img.setimgOtherName(rs.getString("imgOtherName"));
				list.add(img);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 展示图片详情
	 * @param id 图片id
	 * @return List<Product>
	 */
	public List<img> searchHeaderServlet(String keywords){
		// 创建List
		List<img> list = new ArrayList<img>();
		// 获取数据库连接
		Connection conn = getConnection();
		// 分页查询的SQL语句
		String sql = "select * from img where imgOtherName like '%"+keywords+"%'";
		try {
			PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				img img = new img();
				img.setid(rs.getInt("id"));
				img.setimgName(rs.getString("imgName"));
				img.setimgSrc(rs.getString("imgSrc"));
				img.setimgOtherName(rs.getString("imgOtherName"));
				list.add(img);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 获取注册用户类型
	 * @return List<userType>
	 */
	public List<userType> getUserType(){
		// 创建List
		List<userType> list = new ArrayList<userType>();
		// 获取数据库连接
		Connection conn = getConnection();
		// 分页查询的SQL语句
		String sql = "select * from userType order by id desc";
		try {
			PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				userType userType = new userType();
				userType.setid(rs.getInt("id"));
				userType.setuserType(rs.getInt("userType"));
				userType.setuserDescription(rs.getString("userDescription"));
				list.add(userType);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
}
